package com.attendancesystem.service;

import com.attendancesystem.model.Course;
import com.attendancesystem.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseService {
    public List<Course> getCourseList() {

        List<Course> courses = new ArrayList<>();
        String sql = "SELECT * FROM courses limit 8";
        try (
                Connection con = JDBCUtils.getConnection(); // 必须从连接池获取
                PreparedStatement stm = con.prepareStatement(sql);
                ResultSet res = stm.executeQuery();
        ) {
            while (res.next()) {
                Course course = new Course();
                course.setCourseId(res.getInt("course_id"));
                course.setCourseCode(res.getString("course_code"));
                course.setCourseName(res.getString("course_name"));
                course.setCredit(res.getInt("credit"));
                course.setTeacherId(res.getString("teacher_id"));
                courses.add(course);
            }

        }catch (Exception e){
            System.out.println("数据库出现异常");
        }

        return courses;
    }
    public Boolean addCourse(Course course){
        String sql="INSERT INTO courses (course_code, course_name, credit, teacher_id) VALUES (?, ?, ?, ?)";
        try(
                Connection con = JDBCUtils.getConnection(); // 必须从连接池获取
                PreparedStatement stm = con.prepareStatement(sql);
        ){
            stm.setString(1, course.getCourseCode());
            stm.setString(2, course.getCourseName());
            stm.setInt(3, course.getCredit());
            stm.setString(4, course.getTeacherId());
            if (stm.executeUpdate()>0){
                return true;
            }

        }catch (SQLException e){
            System.out.println("数据库出现异常");
        }
        return false;
    }
    public Boolean deleteCourse(int courseId){
       String sql="DELETE FROM courses WHERE course_id=?";
       try(
               Connection con = JDBCUtils.getConnection();
               PreparedStatement stm = con.prepareStatement(sql);

       ){
           stm.setInt(1, courseId);
           if (stm.executeUpdate()>0){
               return true;
           }
       }catch (SQLException e){
           System.out.println("数据库出现异常");
       }
       return false;
    }
    public Boolean updateCourse(Course course){
        String sql="UPDATE courses SET course_code=?, course_name=?, credit=?, teacher_id=? WHERE course_id=?";
        try(
                Connection con = JDBCUtils.getConnection();
                PreparedStatement stm = con.prepareStatement(sql);

        ){
            stm.setString(1, course.getCourseCode());
            stm.setString(2, course.getCourseName());
            stm.setInt(3, course.getCredit());
            stm.setString(4, course.getTeacherId());
            stm.setInt(5, course.getCourseId());
            if (stm.executeUpdate()>0){
                return true;
            }
        }catch (SQLException e){
            System.out.println("数据库出现异常");
        }
        return false;
    }
}
